﻿using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System.Data;
using System.Security.Principal;


namespace Bank_database_system
{

    public static class AccountCheck
    {
        private const string connectionString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=47.96.39.153)(PORT=1521))(CONNECT_DATA=(SID=orcl)));
                                                 User Id=system;
                                                 Password=Tongji123456;
                                                 Connection Timeout=5;";
        public static string staffid = "";
        public static string branchid = "";
        public static string name = "";
        public static string branchname = "";
        public static decimal Balance_Inquiry(String card)
        {
            decimal balance = 0;
            string selectInquire = "select BALANCE FROM ACCOUNT WHERE CREDIT_CARD_NUMBERS = :card";
            // 创建数据库连接对象
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(selectInquire, connection))
                    {

                        command.Parameters.Add("card", OracleDbType.Varchar2).Value = card;

                        // 执行存储过程
                        object result = command.ExecuteScalar();

                        // 获取返回的 balance 值
                        if (result != null&&result!= DBNull.Value)
                        {
                            balance = Convert.ToDecimal(result);
                        }
                    }
                }
            }
            catch (OracleException ex)
            {
                // 处理 Oracle 数据库相关异常
                MessageBox.Show($"数据库操作失败：{ex.Message}", "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                // 处理其他类型的异常
                MessageBox.Show($"发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return balance;
        }
        public static int passwordCheck(String passward,String card)
        {
            // 创建数据库连接对象
            String obpassword="";
            String state = "";
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    using (OracleCommand command = new OracleCommand("SELECT PASSWORD,STATE FROM ACCOUNT WHERE  CREDIT_CARD_NUMBERS = :account", connection))
                    {
                        command.CommandType = CommandType.Text;

                        // 定义输入参数
                        command.Parameters.Add("account", OracleDbType.Varchar2).Value = card;

                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            // 如果有结果
                            if (reader.Read())
                            {
                                obpassword = reader["PASSWORD"].ToString();
                                state = reader["STATE"].ToString();
                            }
                            else
                            {
                                return 0;
                            }
                        }
                    }
                }                
            }
            catch (OracleException ex)
            {
                // 处理 Oracle 数据库相关异常
                MessageBox.Show($"数据库操作失败：{ex.Message}", "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                // 处理其他类型的异常
                MessageBox.Show($"发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            if (PasswordHandler.VerifyPassword(passward, obpassword))
            {
                if (state == "冻结")
                {
                    return 2;
                }
                else
                    return 1;
            }
            else
            {
                return 0;
            }
        }

        public static int accountState(String card)
        {
            // 创建数据库连接对象
            String state = "";
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    using (OracleCommand command = new OracleCommand("SELECT STATE FROM ACCOUNT WHERE  CREDIT_CARD_NUMBERS = :account", connection))
                    {
                        command.CommandType = CommandType.Text;

                        // 定义输入参数
                        command.Parameters.Add("account", OracleDbType.Varchar2).Value = card;

                        // 执行查询
                        object result = command.ExecuteScalar();

                        // 处理结果
                        if (result != null && result != DBNull.Value)
                        {
                            state = result.ToString();
                        }
                        else
                        {
                            return 0;
                        }
                    }
                }

            }
            catch (OracleException ex)
            {
                // 处理 Oracle 数据库相关异常
                MessageBox.Show($"数据库操作失败：{ex.Message}", "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                // 处理其他类型的异常
                MessageBox.Show($"发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return state=="冻结"?1:2;
        }

        public static int staffCheck(String passward, String id)
        {
            // 创建数据库连接对象
            String obpassword = "";
            String state = "";
            String a = "";
            String b = "";
            String d = "";
            int c = 0;
            String e = "";
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    using (OracleCommand command = new OracleCommand("SELECT IDENTIFICATION_NUMBER,BRANCH_ID,DEPARTMENT,SYSTEM_KEY,WORKING_CONDITION,AUTHORITY_LEVEL,BRANCH_NAME FROM STAFF NATURAL JOIN DEPARTMENT_AUTHORITY NATURAL JOIN BRANCH WHERE  STAFF_ID = :account", connection))
                    {
                        command.CommandType = CommandType.Text;

                        // 定义输入参数
                        command.Parameters.Add("account", OracleDbType.Varchar2).Value = id;

                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            // 如果有结果
                            if (reader.Read())
                            {
                                obpassword = reader["SYSTEM_KEY"].ToString();
                                state = reader["WORKING_CONDITION"].ToString();
                                a = reader["BRANCH_ID"].ToString();
                                b = reader["DEPARTMENT"].ToString();
                                c = Convert.ToInt32( reader["AUTHORITY_LEVEL"].ToString());
                                d = reader["BRANCH_NAME"].ToString();
                                e = reader["IDENTIFICATION_NUMBER"].ToString();
                            }
                            else
                            {
                                return -1;
                            }
                        }
                    }
                    if (PasswordHandler.VerifyPassword(passward, obpassword))
                    {
                        if (state != "在职")
                            return 0;
                    }
                    else
                        return -1;
                    using (OracleCommand command = new OracleCommand("SELECT NAME FROM PERSONAL_INFORMATION WHERE  IDENTIFICATION_NUMBER = :num", connection))
                    {
                        command.CommandType = CommandType.Text;

                        // 定义输入参数
                        command.Parameters.Add("num", OracleDbType.Varchar2).Value = e;

                        object reader = command.ExecuteScalar();

                        // 如果有结果
                        if (reader!=null &&reader != DBNull.Value)
                        {
                            name = reader.ToString();
                            branchid = a;
                            branchname = d;
                            staffid = id;
                            return c;
                        }
                        else
                        {
                            return 0;
                        }

                    }

                }
            }
            catch (OracleException ex)
            {
                // 处理 Oracle 数据库相关异常
                MessageBox.Show($"数据库操作失败：{ex.Message}", "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                // 处理其他类型的异常
                MessageBox.Show($"发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return 9;
        }
    }
}
